package com.mysql.imples;




import com.mysql.dao.Staffsdao;
import com.mysql.entity.Staffs;
import com.mysql.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;


/**
 * 作者:wmx  2025-02-08 11:26
 * 1、功能；通用数据交互层-staffs表

 **/
public class Staffsimple implements Staffsdao {
    private static QueryRunner qr = new QueryRunner(DruidUtil.getDataSource()) ;//数据库连接

    /**
     * 功能:mysql-操作数据
     * @param sql  sql语句
     * @return 返回sql防注入语句
     */
     public List<Staffs> executeDataRander() {
        String sql = "SELECT `id`, `name`, `age`, `phone`, `sta_pos` AS staPos, `add_time` AS addTime, `update_time` AS updateTime FROM staffs";        List<Staffs> staffs = null;
        try {
            staffs = qr.query(sql, new BeanListHandler<>(Staffs.class));//new MapListHandler()结果集处理器
        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }
        return  staffs;
    }


    /**
     * 功能:mysql-操作员工数据(添加)
     * @param sql  sql语句
     * @param object 员工信息
     * @return 返回是否修改操作
     */
    public Integer executeDateInsert(Object object) {
        String sql = " insert into staffs values(default,?,?,?,?,default,default)";
        Staffs staffs = (Staffs) object;
        Integer num = null;
        try {
            num = qr.update(sql,staffs.getName(),staffs.getAge(),staffs.getPhone(),staffs.getStaPos());
        } catch (SQLException e) {
            e.printStackTrace();
            return  -1;
        }
        return num;
    }


    /**
     * 功能:mysql-操作数据(删除、修改)
     * @param sql  sql语句
     * @param id   序号
     * @param int 返回是否执行成功
     * @return
     */
    public Integer  executeDateDelete(int id) {
        String sql = "delete from staffs where `id` = ?";
        Integer num = null;
        try {
            num = qr.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }



    /**
     * 功能:mysql-操作员工数据(修改)
     * @param sql  sql语句
     * @param object 员工信息
     * @return 返回是否修改操作
     */

    public Integer executeDateUpdate(Object object ) {
        String sql = "update staffs set  `name` = ? ,`age` = ? ,`phone` = ? ,`sta_pos` = ?   where `id` = ?";
        Integer num = null;
        Staffs staffs = (Staffs) object;
        try {
            num = qr.update(sql,staffs.getName(),staffs.getAge(),staffs.getPhone(),staffs.getStaPos(),staffs.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }


    /**
     * 功能:mysql-获取员工数据模糊查询总数据
     * @param sql  sql语句
     * @param query 模糊查询条件
     * @return 返回员工数据模糊查询总数据
     */
    public List<Staffs> executeDataQuery(String query) {
        String sql = " select `id`,`name`,`age`,`phone`,`sta_pos`,`add_time`,`update_time` from staffs \n" +
                " where \n" +
                "`id` like ? or\n" +
                "`name` like ? or\n" +
                "`age` like ? or\n" +
                "`phone` like ? or\n" +
                "`sta_pos` like ? or\n" +
                "`add_time` like ? or\n" +
                " `update_time` like ? ";
        List<Staffs> staffs = null;
        try {
            String queryWithWildcards = "%" + query + "%";
            staffs = qr.query(sql, new BeanListHandler<>(Staffs.class),
                    queryWithWildcards, queryWithWildcards, queryWithWildcards,
                    queryWithWildcards, queryWithWildcards, queryWithWildcards, queryWithWildcards);//new MapListHandler()结果集处理器
        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }
        return  staffs;
    }


    /**
     * 功能:mysql-获取员工数据总数
     * @param sql sql语句
     * @return 返回员工数据总数
     */
    public Long executeDateCount() {
        String sql = "select count(`id`) from staffs";
        Long page;
        try {
            page = (Long) qr.query(sql, new ScalarHandler());
        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }
        return  page;
    }

    /**
     * 功能:mysql-获取员工数据模糊查询总数据
     * @param sql  sql语句
     * @param query 模糊查询条件
     * @return 返回员工数据模糊查询总数据
     */
    public Long executeDataQueryCount(String query) {
        String sql = " select count(`id`) from staffs \n" +
                " where \n" +
                "`id` like ? or\n" +
                "`name` like ? or\n" +
                "`age` like ? or\n" +
                "`phone` like ? or\n" +
                "`sta_pos` like ? or\n" +
                "`add_time` like ? or\n" +
                " `update_time` like ? ";
        Long page;
        try {
            String queryWithWildcards = "%" + query + "%";
            page = (Long) qr.query(sql, new ScalarHandler(), queryWithWildcards, queryWithWildcards, queryWithWildcards,
                    queryWithWildcards, queryWithWildcards, queryWithWildcards, queryWithWildcards);//new MapListHandler()结果集处理器
        } catch (SQLException e) {
            e.printStackTrace();
            return  null;
        }
        return  page;
    }

}
